<?php
class Order_model extends Model {
	function __construct() {
		parent::Model();
	}
	function add($data) {
		$this->db->insert('orders',$data);
		return $this->db->insert_id();
	}
	function identify($id) {
		$this->db->where('id',$id);
		$query = $this->db->get('orders');
		return $query->first_row('array');
	}
	function update($id,$data) {
		$this->db->where('id',$id);
		$this->db->update('orders',$data);
	}
	function delete($id) {
		$this->db->where('id',$id);
		$this->db->delete('orders');
	}
	function delete_all($customer_id) {
		$this->db->where('customer_id',$customer_id);
		$this->db->delete('orders');
	}
	function last($limit) {
		$this->session->set_userdata('by_typecustomer',1);
		$this->db->order_by('id','desc');
		$this->db->limit($limit);
		$query = $this->db->get('orders');
		/*$type=$this->session->userdata('by_typecustomer');
		if(empty($type))
			$type=1;
		$sql="select orders.*  top $limit from orders, users where users.customer_id=orders.customer_id and users.level='$type' LIMIT $limit";
		$query=$this->db->query($sql);*/
		return $query->result_array();
	}
	function search($customer_name,$order_id,$from_date,$to_date,$by_keyword,$by_status, $by_typecustomer) {
		#$sql = "SELECT * FROM `orders` WHERE";
		if($customer_name || $order_id || $from_date || $to_date || $by_keyword)
		{
			$sql = "Select DISTINCT orders.* from orders,products,carts, users WHERE orders.session_id = carts.session_id AND products.id = carts.product_id AND orders.customer_id = users.customer_id and users.level='$by_typecustomer' ";
			
			if ($customer_name != "")
			{
				$sql .= " AND (orders.storename LIKE '%$customer_name%' || orders.tradingname LIKE '%$customer_name%' || orders.franchisee LIKE '%$customer_name%')";
			
				//$this->db->like('firstname',$customer_name);
				//$this->db->or_like('lastname',$customer_name);
				#$sql .= " (`firstname` LIKE '%$customer_name%' OR `lastname` LIKE '%$customer_name%')";
			} 
			
			if ($order_id != "")
			{
				$sql .= " AND orders.id = '$order_id'";
											
				//$this->db->where('id',$order_id);
				#$sql .= " `id` = $order_id";
			}
			
						
			if ($from_date != "") {
				//$this->db->where("DATEDIFF(DATE(`order_time`),DATE('$from_date')) >",0);
				$sql .= " AND DATEDIFF(DATE(`order_time`),DATE('$from_date'))>=0";
			}
			
			if ($to_date != "") {
				//$this->db->where("DATEDIFF(DATE('$to_date'),DATE(`order_time`)) >",0);
				$sql .= " AND DATEDIFF(DATE('$to_date'),DATE(`order_time`))>=0";
			}
						
			
			if ($by_keyword != "") {
				//do this query - by Haydn
				$sql .= " AND products.title LIKE '%$by_keyword%'";
			}
			if($by_status==1)
				$sql.= " AND orders.status ='successful'";
			$sql.=" order by orders.id desc";
			echo $sql;
			$query = $this->db->query($sql);
			return $query->result_array();
		}	
		else
		{			
			$sql="select orders.* from orders, users where users.customer_id=orders.customer_id and users.level='$by_typecustomer' order by orders.id desc";
			$query = $this->db->query($sql);
			return $query->result_array();
		}
		
	}
	function search_period($period,$value) {
		if($period == "total") {
			$sql = "SELECT * FROM `orders` WHERE `status` = 'successful'";
		} else if ($period == "month") {
			$sql = "SELECT * FROM `orders` WHERE `status` = 'successful'
				AND `order_time` LIKE '$value%'";
		} else if ($period == "week") {
			$sql = "SELECT * FROM `orders` WHERE `status` = 'successful'
				AND DATEDIFF(CURDATE(),DATE(`order_time`)) <= 7";
		} else if ($period == "day") {
			$sql = "SELECT * FROM `orders` WHERE `status` = 'successful'
				AND `order_time` LIKE '$value%'";
		}
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	
	function sales_total() {
		$sql = "SELECT sum(`total`) as `sales` FROM `orders` WHERE `status` = 'successful'";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if ($result['sales'] != NULL) {
			return $result['sales'];
		}
		return '0.00';
	}
	function sales_month($month) {
		$sql = "SELECT sum(`total`) as `sales` FROM `orders` WHERE `status` = 'successful'
				AND `order_time` LIKE '$month%'";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if ($result['sales'] != NULL) {
			return $result['sales'];
		}
		return '0.00';
	}
	function sales_week() {
		$sql = "SELECT sum(`total`) as `sales` FROM `orders` WHERE `status` = 'successful'
				AND DATEDIFF(CURDATE(),DATE(`order_time`)) <= 7";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if ($result['sales'] != NULL) {
			return $result['sales'];
		}
		return '0.00';
	}
	function sales_date($date) {
		$sql = "SELECT sum(`total`) as `sales` FROM `orders` WHERE `status` = 'successful'
				AND `order_time` LIKE '$date%'";
		$query = $this->db->query($sql);
		$result = $query->first_row('array');
		if($result['sales'] != NULL) {
			return $result['sales'];
		}
		return '0.00';
	}
	function get_orders_export()
	{
		$this->db->where('status','successful');
		$this->db->where('exported',0);
		$this->db->order_by('id','desc');
		$query = $this->db->get('orders');
		return $query->result_array();
	}
}
?>